<!DOCTYPE html><html lang="zh-CN"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width, initial-scale=1"><meta name="format-detection" content="telephone=no"><meta name="apple-mobile-web-app-capable" content="yes"><meta name="apple-mobile-web-app-status-bar-style" content="black"><link rel="icon" href="/assets/favicon16.png?v=2.0.1" type="image/png" sizes="16x16"><link rel="icon" href="/assets/favicon32.png?v=2.0.1" type="image/png" sizes="32x32"><meta property="og:type" content="website">
<meta property="og:title" content="随机漫步的傻瓜">
<meta property="og:url" content="https://hoffeechen.gitee.io/index.html">
<meta property="og:site_name" content="随机漫步的傻瓜">
<meta property="og:locale" content="zh_CN">
<meta property="article:author" content="Paradiser">
<meta name="twitter:card" content="summary"><meta name="keywords" content="Paradiser, 随机漫步的傻瓜"><meta name="description" content=""><title>随机漫步的傻瓜</title><link ref="canonical" href="https://hoffeechen.gitee.io/index.html"><link rel="dns-prefetch" href="https://cdn.jsdelivr.net"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free@5.12.1/css/all.min.css" type="text/css"><link rel="stylesheet" href="/css/index.css?v=2.0.1"><script>var Stun = window.Stun || {};
var CONFIG = {
  root: '/',
  algolia: undefined,
  fontIcon: {"prompt":{"success":"fas fa-check-circle","info":"fas fa-arrow-circle-right","warning":"fas fa-exclamation-circle","error":"fas fa-times-circle"},"copyBtn":"fas fa-copy"},
  sidebar: {"offsetTop":"10px","tocMaxDepth":6},
  header: {"enable":true,"showOnPost":true,"scrollDownIcon":false},
  postWidget: {"endText":true},
  nightMode: {"enable":true},
  back2top: {"enable":true},
  codeblock: {"style":"default","highlight":"light","wordWrap":true},
  reward: false,
  fancybox: false,
  zoomImage: {"gapAside":"20px"},
  galleryWaterfall: undefined,
  lazyload: true,
  pjax: {"avoidBanner":true},
  externalLink: {"icon":{"enable":true,"name":"fas fa-external-link-alt"}},
  shortcuts: undefined,
  prompt: {"copyButton":"复制","copySuccess":"复制成功","copyError":"复制失败"},
  sourcePath: {"js":"js","css":"css","images":"images"},
};

window.CONFIG = CONFIG;</script><meta name="generator" content="Hexo 4.2.1"></head><body><div class="container" id="container"><header class="header" id="header"><div class="header-inner"><nav class="header-nav header-nav--fixed"><div class="header-nav-inner"><div class="header-nav-menubtn"><i class="fas fa-bars"></i></div><div class="header-nav-menu"><div class="header-nav-menu-item"><a class="header-nav-menu-item__link" href="/"><span class="header-nav-menu-item__icon"><i class="fas fa-home"></i></span><span class="header-nav-menu-item__text">首页</span></a></div><div class="header-nav-menu-item"><a class="header-nav-menu-item__link" href="/archives/"><span class="header-nav-menu-item__icon"><i class="fas fa-folder-open"></i></span><span class="header-nav-menu-item__text">归档</span></a></div><div class="header-nav-menu-item"><a class="header-nav-menu-item__link" href="/categories/"><span class="header-nav-menu-item__icon"><i class="fas fa-layer-group"></i></span><span class="header-nav-menu-item__text">分类</span></a></div><div class="header-nav-menu-item"><a class="header-nav-menu-item__link" href="/tags/"><span class="header-nav-menu-item__icon"><i class="fas fa-tags"></i></span><span class="header-nav-menu-item__text">标签</span></a></div></div><div class="header-nav-mode"><div class="mode"><div class="mode-track"><span class="mode-track-moon"></span><span class="mode-track-sun"></span></div><div class="mode-thumb"></div></div></div></div></nav><div class="header-banner"><div class="header-banner-info"><div class="header-banner-info__title">随机漫步的傻瓜</div><div class="header-banner-info__subtitle"></div></div></div></div></header><main class="main" id="main"><div class="main-inner"><div class="content-wrap" id="content-wrap"><div class="content content-home" id="content"><section class="postlist"><article class="postlist-item post"><header class="post-header"><h1 class="post-title"><a class="post-title__link" href="/2020/05/29/westwind/20200529/">当东方的太阳升起后，根本不需要什么灯塔</a></h1><div class="post-meta"><span class="post-meta-item post-meta-item--createtime"><span class="post-meta-item__icon"><i class="far fa-calendar-plus"></i></span><span class="post-meta-item__info">发表于</span><span class="post-meta-item__value">2020-05-29</span></span><span class="post-meta-item post-meta-item--updatetime"><span class="post-meta-item__icon"><i class="far fa-calendar-check"></i></span><span class="post-meta-item__info">更新于</span><span class="post-meta-item__value">2020-05-29</span></span><span class="post-meta-item post-meta-item--wordcount"><span class="post-meta-item__icon"><i class="far fa-file-word"></i></span><span class="post-meta-item__info">字数统计</span><span class="post-meta-item__value">2k</span></span><span class="post-meta-item post-meta-item--readtime"><span class="post-meta-item__icon"><i class="far fa-clock"></i></span><span class="post-meta-item__info">阅读时长</span><span class="post-meta-item__value">10分</span></span></div></header><div class="post-body"><div class="post-excerpt">20200529 记忆承载 推文</div><div class="post-readmore"><a class="post-readmore__link" href="/2020/05/29/westwind/20200529/"><span class="post-readmore__text">阅读全文</span><span class="post-readmore__icon"><i class="fas fa-long-arrow-alt-right"></i></span></a></div></div></article><article class="postlist-item post"><header class="post-header"><h1 class="post-title"><a class="post-title__link" href="/2020/04/01/SQL/Hive%20SQL50%E9%81%93%E7%BB%83%E4%B9%A0%E9%A2%98/">Hive SQL50道练习题</a></h1><div class="post-meta"><span class="post-meta-item post-meta-item--createtime"><span class="post-meta-item__icon"><i class="far fa-calendar-plus"></i></span><span class="post-meta-item__info">发表于</span><span class="post-meta-item__value">2020-04-01</span></span><span class="post-meta-item post-meta-item--updatetime"><span class="post-meta-item__icon"><i class="far fa-calendar-check"></i></span><span class="post-meta-item__info">更新于</span><span class="post-meta-item__value">2020-05-29</span></span><span class="post-meta-item post-meta-item--wordcount"><span class="post-meta-item__icon"><i class="far fa-file-word"></i></span><span class="post-meta-item__info">字数统计</span><span class="post-meta-item__value">4.3k</span></span><span class="post-meta-item post-meta-item--readtime"><span class="post-meta-item__icon"><i class="far fa-clock"></i></span><span class="post-meta-item__info">阅读时长</span><span class="post-meta-item__value">44分</span></span></div></header><div class="post-body"><div class="post-excerpt"><p>[TOC]</p>

        <h2 id="建表"   >
          <a href="#建表" class="heading-link"><i class="fas fa-link"></i></a>建表</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">create table student(s_id string,s_name string,s_birth string,s_sex string) row format delimited fields terminated by &#39;\t&#39;;</span><br><span class="line"></span><br><span class="line">create table course(c_id string,c_name string,t_id string) row format delimited fields terminated by &#39;\t&#39;;</span><br><span class="line"></span><br><span class="line">create table teacher(t_id string,t_name string) row format delimited fields terminated by &#39;\t&#39;;</span><br><span class="line"></span><br><span class="line">create table score(s_id string,c_id string,s_score int) row format delimited fields terminated by &#39;\t&#39;;</span><br></pre></td></tr></table></div></figure>


        <h2 id="生成数据"   >
          <a href="#生成数据" class="heading-link"><i class="fas fa-link"></i></a>生成数据</h2>
      <p><code>vi /export/data/hivedatas/student.csv</code></p>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">01 赵雷 1990-01-01 男</span><br><span class="line">02 钱电 1990-12-21 男</span><br><span class="line">03 孙风 1990-05-20 男</span><br><span class="line">04 李云 1990-08-06 男</span><br><span class="line">05 周梅 1991-12-01 女</span><br><span class="line">06 吴兰 1992-03-01 女</span><br><span class="line">07 郑竹 1989-07-01 女</span><br><span class="line">08 王菊 1990-01-20 女</span><br></pre></td></tr></table></div></figure>

<p><code>vi /export/data/hivedatas/course.csv</code></p>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">01  语文  02</span><br><span class="line">02  数学  01</span><br><span class="line">03  英语  03</span><br></pre></td></tr></table></div></figure>

<p><code>vi /export/data/hivedatas/teacher.csv</code></p>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">01  张三</span><br><span class="line">02  李四</span><br><span class="line">03  王五</span><br></pre></td></tr></table></div></figure>

<p><code>vi /export/data/hivedatas/score.csv</code></p>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line">01  01  80</span><br><span class="line">01  02  90</span><br><span class="line">01  03  99</span><br><span class="line">02  01  70</span><br><span class="line">02  02  60</span><br><span class="line">02  03  80</span><br><span class="line">03  01  80</span><br><span class="line">03  02  80</span><br><span class="line">03  03  80</span><br><span class="line">04  01  50</span><br><span class="line">04  02  30</span><br><span class="line">04  03  20</span><br><span class="line">05  01  76</span><br><span class="line">05  02  87</span><br><span class="line">06  01  31</span><br><span class="line">06  03  34</span><br><span class="line">07  02  89</span><br><span class="line">07  03  98</span><br></pre></td></tr></table></div></figure>


        <h2 id="导数据到hive"   >
          <a href="#导数据到hive" class="heading-link"><i class="fas fa-link"></i></a>导数据到hive</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">load data local inpath &#39;&#x2F;export&#x2F;data&#x2F;hivedatas&#x2F;student.csv&#39; into table student;</span><br><span class="line">load data local inpath &#39;&#x2F;export&#x2F;data&#x2F;hivedatas&#x2F;course.csv&#39; into table course;</span><br><span class="line">load data local inpath &#39;&#x2F;export&#x2F;data&#x2F;hivedatas&#x2F;teacher.csv&#39; into table teacher;</span><br><span class="line">load data local inpath &#39;&#x2F;export&#x2F;data&#x2F;hivedatas&#x2F;score.csv&#39; into table score;</span><br></pre></td></tr></table></div></figure>


        <h2 id="hive查询语法"   >
          <a href="#hive查询语法" class="heading-link"><i class="fas fa-link"></i></a>hive查询语法</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">SELECT [ALL | DISTINCT] select_expr, select_expr, ...</span><br><span class="line">    FROM table_reference</span><br><span class="line">    [WHERE where_condition]</span><br><span class="line">    [GROUP BY col_list [HAVING condition]]</span><br><span class="line">    [CLUSTER BY col_list</span><br><span class="line">      | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]</span><br><span class="line">    ]</span><br><span class="line">    [LIMIT number]</span><br></pre></td></tr></table></div></figure>


        <h2 id="1、查询”01”课程比”02”课程成绩高的学生的信息及课程分数"   >
          <a href="#1、查询”01”课程比”02”课程成绩高的学生的信息及课程分数" class="heading-link"><i class="fas fa-link"></i></a>1、查询”01”课程比”02”课程成绩高的学生的信息及课程分数</h2>
      <ul>
<li>方法1</li>
</ul>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">select student.*,a.s_score as 01_score,b.s_score as 02_score</span><br><span class="line">from student</span><br><span class="line">  join score a on student.s_id&#x3D;a.s_id and a.c_id&#x3D;&#39;01&#39;</span><br><span class="line">  left join score b on student.s_id&#x3D;b.s_id and b.c_id&#x3D;&#39;02&#39;</span><br><span class="line">where  a.s_score&gt;b.s_score;</span><br></pre></td></tr></table></div></figure>

<ul>
<li>方法2</li>
</ul>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">select student.*,a.s_score as 01_score,b.s_score as 02_score</span><br><span class="line">from student</span><br><span class="line">join score a on  a.c_id&#x3D;&#39;01&#39;</span><br><span class="line">join score b on  b.c_id&#x3D;&#39;02&#39;</span><br><span class="line">where  a.s_id&#x3D;student.s_id and b.s_id&#x3D;student.s_id and a.s_score&gt;b.s_score;</span><br></pre></td></tr></table></div></figure>


        <h2 id="2、查询”01”课程比”02”课程成绩低的学生的信息及课程分数"   >
          <a href="#2、查询”01”课程比”02”课程成绩低的学生的信息及课程分数" class="heading-link"><i class="fas fa-link"></i></a>2、查询”01”课程比”02”课程成绩低的学生的信息及课程分数</h2>
      <ul>
<li>方法1</li>
</ul>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">select student.*,a.s_score as 01_score,b.s_score as 02_score</span><br><span class="line">from student</span><br><span class="line">join score a on student.s_id&#x3D;a.s_id and a.c_id&#x3D;&#39;01&#39;</span><br><span class="line">left join score b on student.s_id&#x3D;b.s_id and b.c_id&#x3D;&#39;02&#39;</span><br><span class="line">where a.s_score&lt;b.s_score;</span><br></pre></td></tr></table></div></figure>

<ul>
<li>方法2</li>
</ul>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">select student.*,a.s_score as 01_score,b.s_score as 02_score</span><br><span class="line">from student</span><br><span class="line">join score a on  a.c_id&#x3D;&#39;01&#39;</span><br><span class="line">join score b on  b.c_id&#x3D;&#39;02&#39;</span><br><span class="line">where  a.s_id&#x3D;student.s_id and b.s_id&#x3D;student.s_id and a.s_score&lt;b.s_score;</span><br></pre></td></tr></table></div></figure>


        <h2 id="3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩"   >
          <a href="#3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩" class="heading-link"><i class="fas fa-link"></i></a>3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩</h2>
      <ul>
<li>方法1</li>
</ul>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">select  student.s_id,student.s_name,tmp.平均成绩 from student</span><br><span class="line">  join (</span><br><span class="line">    select score.s_id,round(avg(score.s_score),1)as 平均成绩</span><br><span class="line">        from score group by s_id)as tmp</span><br><span class="line">  on tmp.平均成绩&gt;&#x3D;60</span><br><span class="line">where student.s_id &#x3D; tmp.s_id</span><br></pre></td></tr></table></div></figure>

<ul>
<li>方法2</li>
</ul>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">select  student.s_id,student.s_name,round(avg (score.s_score),1) as 平均成绩 from student</span><br><span class="line">join score on student.s_id &#x3D; score.s_id</span><br><span class="line">group by student.s_id,student.s_name</span><br><span class="line">having avg (score.s_score) &gt;&#x3D; 60;</span><br></pre></td></tr></table></div></figure>


        <h2 id="4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩-包括有成绩的和无成绩的"   >
          <a href="#4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩-包括有成绩的和无成绩的" class="heading-link"><i class="fas fa-link"></i></a>4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)</h2>
      <ul>
<li>方法1</li>
</ul>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">select  student.s_id,student.s_name,tmp.avgScore from student</span><br><span class="line">join (</span><br><span class="line">select score.s_id,round(avg(score.s_score),1)as avgScore from score group by s_id)as tmp</span><br><span class="line">on tmp.avgScore &lt; 60</span><br><span class="line">where student.s_id&#x3D;tmp.s_id</span><br><span class="line">union all</span><br><span class="line">select  s2.s_id,s2.s_name,0 as avgScore from student s2</span><br><span class="line">where s2.s_id not in</span><br><span class="line">    (select distinct sc2.s_id from score sc2);</span><br></pre></td></tr></table></div></figure>

<ul>
<li>方法2</li>
</ul>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">select  score.s_id,student.s_name,round(avg (score.s_score),1) as avgScore from student</span><br><span class="line">inner join score on student.s_id&#x3D;score.s_id</span><br><span class="line">group by score.s_id,student.s_name</span><br><span class="line">having avg (score.s_score) &lt; 60</span><br><span class="line">union all</span><br><span class="line">select  s2.s_id,s2.s_name,0 as avgScore from student s2</span><br><span class="line">where s2.s_id not in</span><br><span class="line">    (select distinct sc2.s_id from score sc2);</span><br></pre></td></tr></table></div></figure>


        <h2 id="5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩"   >
          <a href="#5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩" class="heading-link"><i class="fas fa-link"></i></a>5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">select student.s_id,student.s_name,(count(score.c_id) )as total_count,sum(score.s_score)as total_score</span><br><span class="line">from student</span><br><span class="line">left join score on student.s_id&#x3D;score.s_id</span><br><span class="line">group by student.s_id,student.s_name ;</span><br></pre></td></tr></table></div></figure>


        <h2 id="6、查询”李”姓老师的数量"   >
          <a href="#6、查询”李”姓老师的数量" class="heading-link"><i class="fas fa-link"></i></a>6、查询”李”姓老师的数量</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">select t_name,count(1) from teacher  </span><br><span class="line">where t_name like &#39;李%&#39; </span><br><span class="line">group by t_name;</span><br></pre></td></tr></table></div></figure>


        <h2 id="7、查询学过”张三”老师授课的同学的信息"   >
          <a href="#7、查询学过”张三”老师授课的同学的信息" class="heading-link"><i class="fas fa-link"></i></a>7、查询学过”张三”老师授课的同学的信息</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">select student.* from student</span><br><span class="line">join score on student.s_id &#x3D;score.s_id</span><br><span class="line">join  course on course.c_id&#x3D;score.c_id</span><br><span class="line">join  teacher on course.t_id&#x3D;teacher.t_id and t_name&#x3D;&#39;张三&#39;;</span><br></pre></td></tr></table></div></figure>


        <h2 id="8、查询没学过”张三”老师授课的同学的信息"   >
          <a href="#8、查询没学过”张三”老师授课的同学的信息" class="heading-link"><i class="fas fa-link"></i></a>8、查询没学过”张三”老师授课的同学的信息</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">select student.* from student</span><br><span class="line">left join (select s_id from score</span><br><span class="line">      join  course on course.c_id&#x3D;score.c_id</span><br><span class="line">      join  teacher on course.t_id&#x3D;teacher.t_id and t_name&#x3D;&#39;张三&#39;)tmp</span><br><span class="line">on  student.s_id &#x3D;tmp.s_id</span><br><span class="line">where tmp.s_id is null;</span><br></pre></td></tr></table></div></figure>


        <h2 id="9、查询学过编号为”01”并且也学过编号为”02”的课程的同学的信息"   >
          <a href="#9、查询学过编号为”01”并且也学过编号为”02”的课程的同学的信息" class="heading-link"><i class="fas fa-link"></i></a>9、查询学过编号为”01”并且也学过编号为”02”的课程的同学的信息</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">select * from student</span><br><span class="line">join (select s_id from score where c_id &#x3D;1 )tmp1</span><br><span class="line">    on student.s_id&#x3D;tmp1.s_id</span><br><span class="line">join (select s_id from score where c_id &#x3D;2 )tmp2</span><br><span class="line">    on student.s_id&#x3D;tmp2.s_id;</span><br></pre></td></tr></table></div></figure>


        <h2 id="10、查询学过编号为”01”但是没有学过编号为”02”的课程的同学的信息"   >
          <a href="#10、查询学过编号为”01”但是没有学过编号为”02”的课程的同学的信息" class="heading-link"><i class="fas fa-link"></i></a>10、查询学过编号为”01”但是没有学过编号为”02”的课程的同学的信息</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">select student.* from student</span><br><span class="line">join (select s_id from score where c_id &#x3D;1 )tmp1</span><br><span class="line">    on student.s_id&#x3D;tmp1.s_id</span><br><span class="line">left join (select s_id from score where c_id &#x3D;2 )tmp2</span><br><span class="line">    on student.s_id &#x3D;tmp2.s_id</span><br><span class="line">where tmp2.s_id is null;</span><br></pre></td></tr></table></div></figure>


        <h2 id="11、查询没有学全所有课程的同学的信息"   >
          <a href="#11、查询没有学全所有课程的同学的信息" class="heading-link"><i class="fas fa-link"></i></a>11、查询没有学全所有课程的同学的信息</h2>
      <ul>
<li>方法1</li>
</ul>
<p><code>先查询出课程的总数量</code></p>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select count(1) from course;</span><br></pre></td></tr></table></div></figure>

<p><code>再查询所需结果</code></p>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">select student.* from student</span><br><span class="line">left join(</span><br><span class="line">      select s_id</span><br><span class="line">        from score</span><br><span class="line">          group by s_id</span><br><span class="line">            having count(c_id)&#x3D;3)tmp</span><br><span class="line">on student.s_id&#x3D;tmp.s_id</span><br><span class="line">where tmp.s_id is null;</span><br></pre></td></tr></table></div></figure>

<ul>
<li>方法2(一步到位):</li>
</ul>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">select student.* from student</span><br><span class="line">join (select count(c_id)num1 from course)tmp1</span><br><span class="line">left join(</span><br><span class="line">      select s_id,count(c_id)num2</span><br><span class="line">        from score group by s_id)tmp2</span><br><span class="line">on student.s_id&#x3D;tmp2.s_id and tmp1.num1&#x3D;tmp2.num2</span><br><span class="line">where tmp2.s_id is null;</span><br></pre></td></tr></table></div></figure>


        <h2 id="12、查询至少有一门课与学号为”01”的同学所学相同的同学的信息"   >
          <a href="#12、查询至少有一门课与学号为”01”的同学所学相同的同学的信息" class="heading-link"><i class="fas fa-link"></i></a>12、查询至少有一门课与学号为”01”的同学所学相同的同学的信息</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">select student.* from student</span><br><span class="line">join (select c_id from score where score.s_id&#x3D;01)tmp1</span><br><span class="line">join (select s_id,c_id from score)tmp2</span><br><span class="line">    on tmp1.c_id &#x3D;tmp2.c_id and student.s_id &#x3D;tmp2.s_id</span><br><span class="line">where student.s_id  not in(&#39;01&#39;)</span><br><span class="line">group by student.s_id,s_name,s_birth,s_sex;</span><br></pre></td></tr></table></div></figure>


        <h2 id="13、查询和”01”号的同学学习的课程完全相同的其他同学的信息"   >
          <a href="#13、查询和”01”号的同学学习的课程完全相同的其他同学的信息" class="heading-link"><i class="fas fa-link"></i></a>13、查询和”01”号的同学学习的课程完全相同的其他同学的信息</h2>
      <p><code>备注:hive不支持group_concat方法,可用 concat_ws(’|’, collect_set(str)) 实现</code></p>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">select student.*,tmp1.course_id from student</span><br><span class="line">join (select s_id ,concat_ws(&#39;|&#39;, collect_set(c_id)) course_id from score</span><br><span class="line">      group by s_id having s_id not in (1))tmp1</span><br><span class="line">  on student.s_id &#x3D; tmp1.s_id</span><br><span class="line">join (select concat_ws(&#39;|&#39;, collect_set(c_id)) course_id2</span><br><span class="line">            from score  where s_id&#x3D;1)tmp2</span><br><span class="line">      on tmp1.course_id &#x3D; tmp2.course_id2;</span><br></pre></td></tr></table></div></figure>


        <h2 id="14、查询没学过”张三”老师讲授的任一门课程的学生姓名"   >
          <a href="#14、查询没学过”张三”老师讲授的任一门课程的学生姓名" class="heading-link"><i class="fas fa-link"></i></a>14、查询没学过”张三”老师讲授的任一门课程的学生姓名</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">select student.* from student</span><br><span class="line">  left join (select s_id from score</span><br><span class="line">          join (select c_id from course join  teacher on course.t_id&#x3D;teacher.t_id and t_name&#x3D;&#39;张三&#39;)tmp2</span><br><span class="line">          on score.c_id&#x3D;tmp2.c_id )tmp</span><br><span class="line">  on student.s_id &#x3D; tmp.s_id</span><br><span class="line">  where tmp.s_id is null;</span><br></pre></td></tr></table></div></figure>


        <h2 id="15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩"   >
          <a href="#15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩" class="heading-link"><i class="fas fa-link"></i></a>15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">select student.s_id,student.s_name,tmp.avg_score from student</span><br><span class="line">inner join (select s_id from score</span><br><span class="line">      where s_score&lt;60</span><br><span class="line">        group by score.s_id having count(s_id)&gt;1)tmp2</span><br><span class="line">on student.s_id &#x3D; tmp2.s_id</span><br><span class="line">left join (</span><br><span class="line">    select s_id,round(AVG (score.s_score)) avg_score</span><br><span class="line">      from score group by s_id)tmp</span><br><span class="line">      on tmp.s_id&#x3D;student.s_id;</span><br></pre></td></tr></table></div></figure>


        <h2 id="16、检索”01”课程分数小于60，按分数降序排列的学生信息"   >
          <a href="#16、检索”01”课程分数小于60，按分数降序排列的学生信息" class="heading-link"><i class="fas fa-link"></i></a>16、检索”01”课程分数小于60，按分数降序排列的学生信息</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">select student.*,s_score from student,score</span><br><span class="line">where student.s_id&#x3D;score.s_id and s_score&lt;60 and c_id&#x3D;&#39;01&#39;</span><br><span class="line">order by s_score desc;</span><br></pre></td></tr></table></div></figure>


        <h2 id="17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩"   >
          <a href="#17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩" class="heading-link"><i class="fas fa-link"></i></a>17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">select a.s_id,tmp1.s_score as chinese,tmp2.s_score as math,tmp3.s_score as english,</span><br><span class="line">    round(avg (a.s_score),2) as avgScore</span><br><span class="line">from score a</span><br><span class="line">left join (select s_id,s_score  from score s1 where  c_id&#x3D;&#39;01&#39;)tmp1 on  tmp1.s_id&#x3D;a.s_id</span><br><span class="line">left join (select s_id,s_score  from score s2 where  c_id&#x3D;&#39;02&#39;)tmp2 on  tmp2.s_id&#x3D;a.s_id</span><br><span class="line">left join (select s_id,s_score  from score s3 where  c_id&#x3D;&#39;03&#39;)tmp3 on  tmp3.s_id&#x3D;a.s_id</span><br><span class="line">group by a.s_id,tmp1.s_score,tmp2.s_score,tmp3.s_score order by avgScore desc;</span><br></pre></td></tr></table></div></figure>


        <h2 id="18、查询各科成绩最高分、最低分和平均分"   >
          <a href="#18、查询各科成绩最高分、最低分和平均分" class="heading-link"><i class="fas fa-link"></i></a>18、查询各科成绩最高分、最低分和平均分</h2>
      <p><code>以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率</code></p>
<p><code>及格为&gt;=60，中等为：70-80，优良为：80-90，优秀为：&gt;=90</code></p>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">select course.c_id,course.c_name,tmp.maxScore,tmp.minScore,tmp.avgScore,tmp.passRate,tmp.moderate,tmp.goodRate,tmp.excellentRates from course</span><br><span class="line">join(select c_id,max(s_score) as maxScore,min(s_score)as minScore,</span><br><span class="line">    round(avg(s_score),2) avgScore,</span><br><span class="line">    round(sum(case when s_score&gt;&#x3D;60 then 1 else 0 end)&#x2F;count(c_id),2)passRate,</span><br><span class="line">    round(sum(case when s_score&gt;&#x3D;60 and s_score&lt;70 then 1 else 0 end)&#x2F;count(c_id),2) moderate,</span><br><span class="line">    round(sum(case when s_score&gt;&#x3D;70 and s_score&lt;80 then 1 else 0 end)&#x2F;count(c_id),2) goodRate,</span><br><span class="line">    round(sum(case when s_score&gt;&#x3D;80 and s_score&lt;90 then 1 else 0 end)&#x2F;count(c_id),2) excellentRates</span><br><span class="line">from score group by c_id)tmp on tmp.c_id&#x3D;course.c_id;</span><br></pre></td></tr></table></div></figure>


        <h2 id="19、按各科成绩进行排序，并显示排名"   >
          <a href="#19、按各科成绩进行排序，并显示排名" class="heading-link"><i class="fas fa-link"></i></a>19、按各科成绩进行排序，并显示排名</h2>
      <p><code>row_number() over()分组排序功能(mysql没有该方法)</code></p>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">select s1.*,row_number()over(order by s1.s_score desc) Ranking</span><br><span class="line">    from score s1 where s1.c_id&#x3D;&#39;01&#39;order by noRanking asc</span><br><span class="line">union all select s2.*,row_number()over(order by s2.s_score desc) Ranking</span><br><span class="line">    from score s2 where s2.c_id&#x3D;&#39;02&#39;order by noRanking asc</span><br><span class="line">union all select s3.*,row_number()over(order by s3.s_score desc) Ranking</span><br><span class="line">    from score s3 where s3.c_id&#x3D;&#39;03&#39;order by noRanking asc;</span><br></pre></td></tr></table></div></figure>


        <h2 id="20、查询学生的总成绩并进行排名"   >
          <a href="#20、查询学生的总成绩并进行排名" class="heading-link"><i class="fas fa-link"></i></a>20、查询学生的总成绩并进行排名</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">select score.s_id,s_name,sum(s_score) sumscore,row_number()over(order by sum(s_score) desc) Ranking</span><br><span class="line">  from score ,student</span><br><span class="line">    where score.s_id&#x3D;student.s_id</span><br><span class="line">    group by score.s_id,s_name order by sumscore desc;</span><br></pre></td></tr></table></div></figure>


        <h2 id="21、查询不同老师所教不同课程平均分从高到低显示"   >
          <a href="#21、查询不同老师所教不同课程平均分从高到低显示" class="heading-link"><i class="fas fa-link"></i></a>21、查询不同老师所教不同课程平均分从高到低显示</h2>
      <ul>
<li>方法1</li>
</ul>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">select course.c_id,course.t_id,t_name,round(avg(s_score),2)as avgscore from course</span><br><span class="line">    join teacher on teacher.t_id&#x3D;course.t_id</span><br><span class="line">    join score on course.c_id&#x3D;score.c_id</span><br><span class="line">    group by course.c_id,course.t_id,t_name order by avgscore desc;</span><br></pre></td></tr></table></div></figure>

<ul>
<li>方法2</li>
</ul>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">select course.c_id,course.t_id,t_name,round(avg(s_score),2)as avgscore from course,teacher,score</span><br><span class="line">   where teacher.t_id&#x3D;course.t_id and course.c_id&#x3D;score.c_id</span><br><span class="line">    group by course.c_id,course.t_id,t_name order by avgscore desc;</span><br></pre></td></tr></table></div></figure>


        <h2 id="22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩"   >
          <a href="#22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩" class="heading-link"><i class="fas fa-link"></i></a>22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">select tmp1.* from</span><br><span class="line">    (select * from score where c_id&#x3D;&#39;01&#39; order by s_score desc limit 3)tmp1</span><br><span class="line">    order by s_score asc limit 2</span><br><span class="line">union all select tmp2.* from</span><br><span class="line">    (select * from score where c_id&#x3D;&#39;02&#39; order by s_score desc limit 3)tmp2</span><br><span class="line">    order by s_score asc limit 2</span><br><span class="line">union all select tmp3.* from</span><br><span class="line">    (select * from score where c_id&#x3D;&#39;03&#39; order by s_score desc limit 3)tmp3</span><br><span class="line">    order by s_score asc limit 2;</span><br></pre></td></tr></table></div></figure>


        <h2 id="23、统计各科成绩各分数段人数：课程编号-课程名称-100-85-85-70-70-60-0-60-及所占百分比"   >
          <a href="#23、统计各科成绩各分数段人数：课程编号-课程名称-100-85-85-70-70-60-0-60-及所占百分比" class="heading-link"><i class="fas fa-link"></i></a>23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br></pre></td><td class="code"><pre><span class="line">select </span><br><span class="line">    c.c_id,</span><br><span class="line">    c.c_name,</span><br><span class="line">    tmp1.s0_60, </span><br><span class="line">    tmp1.percentum,tmp2.s60_70, </span><br><span class="line">    tmp2.percentum,tmp3.s70_85, </span><br><span class="line">    tmp3.percentum,tmp4.s85_100, </span><br><span class="line">    tmp4.percentum</span><br><span class="line">from </span><br><span class="line">    course c</span><br><span class="line">    join</span><br><span class="line">    (</span><br><span class="line">        select </span><br><span class="line">        c_id,</span><br><span class="line">        sum(case when s_score&lt;60 then 1 else 0 end )as s0_60,</span><br><span class="line">        round(100*sum(case when s_score&lt;60 then 1 else 0 end )&#x2F;count(c_id),2)as percentum</span><br><span class="line">        from score group by c_id</span><br><span class="line">    )tmp1 </span><br><span class="line">    on tmp1.c_id &#x3D;c.c_id</span><br><span class="line">    left join</span><br><span class="line">    (</span><br><span class="line">        select c_id,</span><br><span class="line">        sum(case when s_score&lt;70 and s_score&gt;&#x3D;60 then 1 else 0 end )as s60_70,</span><br><span class="line">        round(100*sum(case when s_score&lt;70 and s_score&gt;&#x3D;60 then 1 else 0 end )&#x2F;count(c_id),2)as percentum</span><br><span class="line">        from score group by c_id</span><br><span class="line">    )tmp2 </span><br><span class="line">    on tmp2.c_id &#x3D;c.c_id</span><br><span class="line">    left join</span><br><span class="line">    (</span><br><span class="line">        select c_id,</span><br><span class="line">        sum(case when s_score&lt;85 and s_score&gt;&#x3D;70 then 1 else 0 end )as s70_85,</span><br><span class="line">        round(100*sum(case when s_score&lt;85 and s_score&gt;&#x3D;70 then 1 else 0 end )&#x2F;count(c_id),2)as percentum</span><br><span class="line">        from score group by c_id</span><br><span class="line">    )tmp3 </span><br><span class="line">    on tmp3.c_id &#x3D;c.c_id</span><br><span class="line">    left join</span><br><span class="line">    (</span><br><span class="line">        select c_id,</span><br><span class="line">        sum(case when s_score&gt;&#x3D;85 then 1 else 0 end )as s85_100,</span><br><span class="line">        round(100*sum(case when s_score&gt;&#x3D;85 then 1 else 0 end )&#x2F;count(c_id),2)as percentum</span><br><span class="line">        from score group by c_id</span><br><span class="line">    )tmp4 </span><br><span class="line">    on tmp4.c_id &#x3D;c.c_id;</span><br></pre></td></tr></table></div></figure>


        <h2 id="24、查询学生平均成绩及其名次"   >
          <a href="#24、查询学生平均成绩及其名次" class="heading-link"><i class="fas fa-link"></i></a>24、查询学生平均成绩及其名次</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">select tmp.*,row_number()over(order by tmp.avgScore desc) Ranking from</span><br><span class="line">  (select student.s_id,</span><br><span class="line">          student.s_name,</span><br><span class="line">          round(avg(score.s_score),2) as avgScore</span><br><span class="line">  from student join score</span><br><span class="line">  on student.s_id&#x3D;score.s_id</span><br><span class="line">  group by student.s_id,student.s_name)tmp</span><br><span class="line">order by avgScore desc;</span><br></pre></td></tr></table></div></figure>


        <h2 id="25、查询各科成绩前三名的记录"   >
          <a href="#25、查询各科成绩前三名的记录" class="heading-link"><i class="fas fa-link"></i></a>25、查询各科成绩前三名的记录</h2>
      <ul>
<li>课程id为01的前三名</li>
</ul>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">select score.c_id,course.c_name,student.s_name,s_score from score</span><br><span class="line">join student on student.s_id&#x3D;score.s_id</span><br><span class="line">join course on  score.c_id&#x3D;&#39;01&#39; and course.c_id&#x3D;score.c_id</span><br><span class="line">order by s_score desc limit 3;</span><br></pre></td></tr></table></div></figure>

<ul>
<li>课程id为02的前三名</li>
</ul>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">select score.c_id,course.c_name,student.s_name,s_score </span><br><span class="line">from score</span><br><span class="line">join student on student.s_id&#x3D;score.s_id</span><br><span class="line">join course on  score.c_id&#x3D;&#39;02&#39; and course.c_id&#x3D;score.c_id</span><br><span class="line">order by s_score desc limit 3;</span><br></pre></td></tr></table></div></figure>

<ul>
<li>课程id为03的前三名</li>
</ul>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">select score.c_id,course.c_name,student.s_name,s_score </span><br><span class="line">from score</span><br><span class="line">join student on student.s_id&#x3D;score.s_id</span><br><span class="line">join course on  score.c_id&#x3D;&#39;03&#39; and course.c_id&#x3D;score.c_id  </span><br><span class="line">order by s_score desc limit 3;</span><br></pre></td></tr></table></div></figure>


        <h2 id="26、查询每门课程被选修的学生数"   >
          <a href="#26、查询每门课程被选修的学生数" class="heading-link"><i class="fas fa-link"></i></a>26、查询每门课程被选修的学生数</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">select c.c_id,c.c_name,tmp.number from course c</span><br><span class="line">    join (select c_id,count(1) as number from score</span><br><span class="line">        where score.s_score&lt;60 group by score.c_id)tmp</span><br><span class="line">    on tmp.c_id&#x3D;c.c_id;</span><br></pre></td></tr></table></div></figure>


        <h2 id="27、查询出只有两门课程的全部学生的学号和姓名"   >
          <a href="#27、查询出只有两门课程的全部学生的学号和姓名" class="heading-link"><i class="fas fa-link"></i></a>27、查询出只有两门课程的全部学生的学号和姓名</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">select st.s_id,st.s_name from student st</span><br><span class="line">  join (select s_id from score group by s_id having count(c_id) &#x3D;2)tmp</span><br><span class="line">    on st.s_id&#x3D;tmp.s_id;</span><br></pre></td></tr></table></div></figure>


        <h2 id="28、查询男生、女生人数"   >
          <a href="#28、查询男生、女生人数" class="heading-link"><i class="fas fa-link"></i></a>28、查询男生、女生人数</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">select tmp1.man,tmp2.women from</span><br><span class="line">    (select count(1) as man from student where s_sex&#x3D;&#39;男&#39;)tmp1,</span><br><span class="line">    (select count(1) as women from student where s_sex&#x3D;&#39;女&#39;)tmp2;</span><br></pre></td></tr></table></div></figure>


        <h2 id="29、查询名字中含有”风”字的学生信息"   >
          <a href="#29、查询名字中含有”风”字的学生信息" class="heading-link"><i class="fas fa-link"></i></a>29、查询名字中含有”风”字的学生信息</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select * from student where s_name like &#39;%风%&#39;;</span><br></pre></td></tr></table></div></figure>


        <h2 id="30、查询同名同性学生名单，并统计同名人数"   >
          <a href="#30、查询同名同性学生名单，并统计同名人数" class="heading-link"><i class="fas fa-link"></i></a>30、查询同名同性学生名单，并统计同名人数</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">select s1.s_id,s1.s_name,s1.s_sex,count(*) as sameName</span><br><span class="line">from student s1,student s2</span><br><span class="line">where s1.s_name&#x3D;s2.s_name and s1.s_id&lt;&gt;s2.s_id and s1.s_sex&#x3D;s2.s_sex</span><br><span class="line">group by s1.s_id,s1.s_name,s1.s_sex;</span><br></pre></td></tr></table></div></figure>


        <h2 id="31、查询1990年出生的学生名单"   >
          <a href="#31、查询1990年出生的学生名单" class="heading-link"><i class="fas fa-link"></i></a>31、查询1990年出生的学生名单</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select * from student where s_birth like &#39;1990%&#39;;</span><br></pre></td></tr></table></div></figure>


        <h2 id="32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列"   >
          <a href="#32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列" class="heading-link"><i class="fas fa-link"></i></a>32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">select score.c_id,c_name,round(avg(s_score),2) as avgScore from score</span><br><span class="line">  join course on score.c_id&#x3D;course.c_id</span><br><span class="line">    group by score.c_id,c_name order by avgScore desc,score.c_id asc;</span><br></pre></td></tr></table></div></figure>


        <h2 id="33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩"   >
          <a href="#33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩" class="heading-link"><i class="fas fa-link"></i></a>33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">select score.s_id,s_name,round(avg(s_score),2)as avgScore from score</span><br><span class="line">    join student on student.s_id&#x3D;score.s_id</span><br><span class="line">    group by score.s_id,s_name having avg(s_score) &gt;&#x3D; 85;</span><br></pre></td></tr></table></div></figure>


        <h2 id="34、查询课程名称为”数学”，且分数低于60的学生姓名和分数"   >
          <a href="#34、查询课程名称为”数学”，且分数低于60的学生姓名和分数" class="heading-link"><i class="fas fa-link"></i></a>34、查询课程名称为”数学”，且分数低于60的学生姓名和分数</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">select s_name,s_score as mathScore from student</span><br><span class="line">    join (select s_id,s_score</span><br><span class="line">            from score,course</span><br><span class="line">            where score.c_id&#x3D;course.c_id and c_name&#x3D;&#39;数学&#39;)tmp</span><br><span class="line">    on tmp.s_score &lt; 60 and student.s_id&#x3D;tmp.s_id;</span><br></pre></td></tr></table></div></figure>


        <h2 id="35、查询所有学生的课程及分数情况"   >
          <a href="#35、查询所有学生的课程及分数情况" class="heading-link"><i class="fas fa-link"></i></a>35、查询所有学生的课程及分数情况</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">select a.s_name,</span><br><span class="line">    SUM(case c.c_name when &#39;语文&#39; then b.s_score else 0 end ) as chainese,</span><br><span class="line">    SUM(case c.c_name when &#39;数学&#39; then b.s_score else 0 end ) as math,</span><br><span class="line">    SUM(case c.c_name when &#39;英语&#39; then b.s_score else 0 end ) as english,</span><br><span class="line">    SUM(b.s_score) as sumScore</span><br><span class="line">  from student a</span><br><span class="line">    join score b on a.s_id&#x3D;b.s_id</span><br><span class="line">    join course c on b.c_id&#x3D;c.c_id</span><br><span class="line">    group by s_name,a.s_id;</span><br></pre></td></tr></table></div></figure>


        <h2 id="36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数"   >
          <a href="#36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数" class="heading-link"><i class="fas fa-link"></i></a>36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">select student.s_id,s_name,c_name,s_score from student</span><br><span class="line">  join (select sc.* from score sc</span><br><span class="line">        left join(select s_id from score where s_score &lt; 70 group by s_id)tmp</span><br><span class="line">        on sc.s_id&#x3D;tmp.s_id where tmp.s_id is null)tmp2</span><br><span class="line">    on student.s_id&#x3D;tmp2.s_id</span><br><span class="line">  join course on tmp2.c_id&#x3D;course.c_id</span><br><span class="line">order by s_id;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">**-- 查询全部及格的信息**</span><br><span class="line">select sc.* from score sc</span><br><span class="line">  left join(select s_id from score where s_score &lt; 60 group by s_id)tmp</span><br><span class="line">    on sc.s_id&#x3D;tmp.s_id</span><br><span class="line">where  tmp.s_id is  null;</span><br><span class="line">**-- 或(效率低)**</span><br><span class="line">select sc.* from score sc</span><br><span class="line">where sc.s_id not in (select s_id from score where s_score &lt; 60 group by s_id);</span><br></pre></td></tr></table></div></figure>


        <h2 id="37、查询课程不及格的学生"   >
          <a href="#37、查询课程不及格的学生" class="heading-link"><i class="fas fa-link"></i></a>37、查询课程不及格的学生</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">select s_name,c_name as courseName,tmp.s_score</span><br><span class="line">from student</span><br><span class="line">join (select s_id,s_score,c_name</span><br><span class="line">      from score,course</span><br><span class="line">      where score.c_id&#x3D;course.c_id and s_score &lt; 60)tmp</span><br><span class="line">on student.s_id&#x3D;tmp.s_id;</span><br></pre></td></tr></table></div></figure>


        <h2 id="38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名"   >
          <a href="#38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名" class="heading-link"><i class="fas fa-link"></i></a>38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">select student.s_id,s_name,s_score as score_01</span><br><span class="line">from student</span><br><span class="line">join score on student.s_id&#x3D;score.s_id</span><br><span class="line">where c_id&#x3D;&#39;01&#39; and s_score &gt;&#x3D; 80;</span><br></pre></td></tr></table></div></figure>


        <h2 id="39、求每门课程的学生人数"   >
          <a href="#39、求每门课程的学生人数" class="heading-link"><i class="fas fa-link"></i></a>39、求每门课程的学生人数</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">select course.c_id,course.c_name,count(1)as selectNum</span><br><span class="line">from course</span><br><span class="line">join score on course.c_id&#x3D;score.c_id</span><br><span class="line">group by course.c_id,course.c_name;</span><br></pre></td></tr></table></div></figure>


        <h2 id="40、查询选修”张三”老师所授课程的学生中，成绩最高的学生信息及其成绩"   >
          <a href="#40、查询选修”张三”老师所授课程的学生中，成绩最高的学生信息及其成绩" class="heading-link"><i class="fas fa-link"></i></a>40、查询选修”张三”老师所授课程的学生中，成绩最高的学生信息及其成绩</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">select student.*,tmp3.c_name,tmp3.maxScore</span><br><span class="line">from (select s_id,c_name,max(s_score)as maxScore from score</span><br><span class="line">      join (select course.c_id,c_name from course join</span><br><span class="line">                  (select t_id,t_name from teacher where t_name&#x3D;&#39;张三&#39;)tmp</span><br><span class="line">            on course.t_id&#x3D;tmp.t_id)tmp2</span><br><span class="line">      on score.c_id&#x3D;tmp2.c_id group by score.s_id,c_name</span><br><span class="line">      order by maxScore desc limit 1)tmp3</span><br><span class="line">join student</span><br><span class="line">on student.s_id&#x3D;tmp3.s_id;</span><br></pre></td></tr></table></div></figure>


        <h2 id="41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩"   >
          <a href="#41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩" class="heading-link"><i class="fas fa-link"></i></a>41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">select distinct a.s_id,a.c_id,a.s_score from score a,score b</span><br><span class="line">    where a.c_id &lt;&gt; b.c_id and a.s_score&#x3D;b.s_score;</span><br></pre></td></tr></table></div></figure>


        <h2 id="42、查询每门课程成绩最好的前三名"   >
          <a href="#42、查询每门课程成绩最好的前三名" class="heading-link"><i class="fas fa-link"></i></a>42、查询每门课程成绩最好的前三名</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">select tmp1.* from</span><br><span class="line">  (select *,row_number()over(order by s_score desc) ranking</span><br><span class="line">      from score  where c_id &#x3D;&#39;01&#39;)tmp1</span><br><span class="line">where tmp1.ranking &lt;&#x3D; 3</span><br><span class="line">union all</span><br><span class="line">select tmp2.* from</span><br><span class="line">  (select *,row_number()over(order by s_score desc) ranking</span><br><span class="line">      from score where c_id &#x3D;&#39;02&#39;)tmp2</span><br><span class="line">where tmp2.ranking &lt;&#x3D; 3</span><br><span class="line">union all</span><br><span class="line">select tmp3.* from</span><br><span class="line">  (select *,row_number()over(order by s_score desc) ranking</span><br><span class="line">      from score where c_id &#x3D;&#39;03&#39;)tmp3</span><br><span class="line">where tmp3.ranking &lt;&#x3D; 3;</span><br></pre></td></tr></table></div></figure>


        <h2 id="43、统计每门课程的学生选修人数（超过5人的课程才统计）"   >
          <a href="#43、统计每门课程的学生选修人数（超过5人的课程才统计）" class="heading-link"><i class="fas fa-link"></i></a>43、统计每门课程的学生选修人数（超过5人的课程才统计）</h2>
      <p><code>要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列</code></p>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">select distinct course.c_id,tmp.num from course</span><br><span class="line">    join (select c_id,count(1) as num from score group by c_id)tmp</span><br><span class="line">    where tmp.num&gt;&#x3D;5 order by tmp.num desc ,course.c_id asc;</span><br></pre></td></tr></table></div></figure>


        <h2 id="44、检索至少选修两门课程的学生学号"   >
          <a href="#44、检索至少选修两门课程的学生学号" class="heading-link"><i class="fas fa-link"></i></a>44、检索至少选修两门课程的学生学号</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">select s_id,count(c_id) as totalCourse</span><br><span class="line">from score</span><br><span class="line">group by s_id</span><br><span class="line">having count(c_id) &gt;&#x3D; 2;</span><br></pre></td></tr></table></div></figure>


        <h2 id="45、查询选修了全部课程的学生信息"   >
          <a href="#45、查询选修了全部课程的学生信息" class="heading-link"><i class="fas fa-link"></i></a>45、查询选修了全部课程的学生信息</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">select student.* </span><br><span class="line">from student,</span><br><span class="line">     (select s_id,count(c_id) as totalCourse </span><br><span class="line">      from score group by s_id)tmp</span><br><span class="line">where student.s_id&#x3D;tmp.s_id and totalCourse&#x3D;3;</span><br></pre></td></tr></table></div></figure>


        <h2 id="46、查询各学生的年龄-周岁"   >
          <a href="#46、查询各学生的年龄-周岁" class="heading-link"><i class="fas fa-link"></i></a>46、查询各学生的年龄(周岁)</h2>
      <p><code>按照出生日期来算，当前月日 &lt; 出生年月的月日则，年龄减一</code></p>
<ul>
<li>方法1</li>
</ul>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">select s_name,s_birth,</span><br><span class="line">      (year(CURRENT_DATE)-year(s_birth)-</span><br><span class="line">        (case when month(CURRENT_DATE) &lt; month(s_birth) then 1</span><br><span class="line">          when month(CURRENT_DATE) &#x3D; month(s_birth) and day(CURRENT_DATE) &lt; day(s_birth) then 1</span><br><span class="line">          else 0 end)</span><br><span class="line">        ) as age</span><br><span class="line">    from student;</span><br></pre></td></tr></table></div></figure>

<ul>
<li>方法2</li>
</ul>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">select s_name,s_birth,</span><br><span class="line">  floor((datediff(current_date,s_birth) - floor((year(current_date) - year(s_birth))&#x2F;4))&#x2F;365) as age</span><br><span class="line">from student;</span><br></pre></td></tr></table></div></figure>


        <h2 id="47、查询本周过生日的学生"   >
          <a href="#47、查询本周过生日的学生" class="heading-link"><i class="fas fa-link"></i></a>47、查询本周过生日的学生</h2>
      <ul>
<li>方法1</li>
</ul>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select * from student where weekofyear(CURRENT_DATE)+1 &#x3D;weekofyear(s_birth);</span><br></pre></td></tr></table></div></figure>

<ul>
<li>方法2</li>
</ul>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">select s_name,s_sex,s_birth from student</span><br><span class="line">    where substring(s_birth,6,2)&#x3D;&#39;10&#39;</span><br><span class="line">    and substring(s_birth,9,2)&#x3D;14;</span><br></pre></td></tr></table></div></figure>


        <h2 id="48、查询下周过生日的学生"   >
          <a href="#48、查询下周过生日的学生" class="heading-link"><i class="fas fa-link"></i></a>48、查询下周过生日的学生</h2>
      <ul>
<li>方法1</li>
</ul>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select * from student where weekofyear(CURRENT_DATE)+1 &#x3D;weekofyear(s_birth);</span><br></pre></td></tr></table></div></figure>

<ul>
<li>方法2</li>
</ul>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">select s_name,s_sex,s_birth from student</span><br><span class="line">    where substring(s_birth,6,2)&#x3D;&#39;10&#39;</span><br><span class="line">    and substring(s_birth,9,2)&gt;&#x3D;15</span><br><span class="line">    and substring(s_birth,9,2)&lt;&#x3D;21;</span><br></pre></td></tr></table></div></figure>


        <h2 id="49、查询本月过生日的学生"   >
          <a href="#49、查询本月过生日的学生" class="heading-link"><i class="fas fa-link"></i></a>49、查询本月过生日的学生</h2>
      <ul>
<li>方法1</li>
</ul>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select * from student where MONTH(CURRENT_DATE) &#x3D;MONTH(s_birth);</span><br></pre></td></tr></table></div></figure>

<ul>
<li>方法2</li>
</ul>
<figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select s_name,s_sex,s_birth from student where substring(s_birth,6,2)&#x3D;&#39;10&#39;;</span><br></pre></td></tr></table></div></figure>


        <h2 id="50、查询12月份过生日的学生"   >
          <a href="#50、查询12月份过生日的学生" class="heading-link"><i class="fas fa-link"></i></a>50、查询12月份过生日的学生</h2>
      <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select s_name,s_sex,s_birth from student where substring(s_birth,6,2)&#x3D;&#39;12&#39;;</span><br></pre></td></tr></table></div></figure>


</div></div></article></section><nav class="paginator"><div class="paginator-inner"><span class="page-number current">1</span></div></nav></div></div><div class="sidebar-wrap" id="sidebar-wrap"><aside class="sidebar" id="sidebar"><section class="sidebar-toc hide"></section><!-- ov = overview--><section class="sidebar-ov"><div class="sidebar-ov-author"><div class="sidebar-ov-author__avatar"><img class="sidebar-ov-author__avatar_img" src="/images/icons/stun-logo.svg" alt="avatar"></div><p class="sidebar-ov-author__text">纸短情长</p></div><div class="sidebar-ov-social"><a class="sidebar-ov-social-item" href="https://github.com/chenhefei/" target="_blank" rel="noopener" data-popover="Github" data-popover-pos="up"><span class="sidebar-ov-social-item__icon"><i class="fab fa-github"></i></span></a></div><div class="sidebar-ov-state"><a class="sidebar-ov-state-item sidebar-ov-state-item--posts" href="/archives/"><div class="sidebar-ov-state-item__count">2</div><div class="sidebar-ov-state-item__name">归档</div></a><a class="sidebar-ov-state-item sidebar-ov-state-item--categories" href="/categories/"><div class="sidebar-ov-state-item__count">3</div><div class="sidebar-ov-state-item__name">分类</div></a><a class="sidebar-ov-state-item sidebar-ov-state-item--tags" href="/tags/"><div class="sidebar-ov-state-item__count">4</div><div class="sidebar-ov-state-item__name">标签</div></a></div><div class="sidebar-ov-cc"><a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.en" target="_blank" rel="noopener" data-popover="知识共享许可协议" data-popover-pos="up"><img src="/images/cc-by-nc-sa.svg"></a></div></section></aside></div><div class="clearfix"></div></div></main><footer class="footer" id="footer"><div class="footer-inner"><div><span>Copyright © 2020</span><span class="footer__icon"><i class="fas fa-heart"></i></span><span>Paradiser</span></div><div><span>由 <a href="http://hexo.io/" title="Hexo" target="_blank" rel="noopener">Hexo</a> 强力驱动</span><span> v4.2.1</span><span class="footer__devider">|</span><span>主题 - <a href="https://github.com/liuyib/hexo-theme-stun/" title="Stun" target="_blank" rel="noopener">Stun</a></span><span> v2.0.1</span></div></div></footer><div class="loading-bar" id="loading-bar"><div class="loading-bar__progress"></div></div><div class="back2top" id="back2top"><span class="back2top__icon"><i class="fas fa-rocket"></i></span></div></div><script src="https://cdn.jsdelivr.net/npm/jquery@v3.4.1/dist/jquery.min.js"></script><script src="https://cdn.jsdelivr.net/npm/velocity-animate@1.5.2/velocity.min.js"></script><script src="https://cdn.jsdelivr.net/npm/velocity-animate@1.5.2/velocity.ui.min.js"></script><script src="https://cdn.jsdelivr.net/npm/lazyload@2.0.0-rc.2/lazyload.min.js"></script><script src="https://cdn.jsdelivr.net/npm/pjax@latest/pjax.min.js"></script><script>window.addEventListener('DOMContentLoaded', function () {
  var pjax = new Pjax({"selectors":["head title","#main",".pjax-reload"],"history":true,"scrollTo":false,"scrollRestoration":false,"cacheBust":false,"debug":false,"currentUrlFullReload":false,"timeout":0});
  // 加载进度条的计时器
  var loadingTimer = null;

  // 重置页面 Y 方向上的滚动偏移量
  document.addEventListener('pjax:send', function () {
    $('.header-nav-menu').removeClass('show');
    if (CONFIG.pjax && CONFIG.pjax.avoidBanner) {
      $('html').velocity('scroll', {
        duration: 500,
        offset: $('#header').height(),
        easing: 'easeInOutCubic'
      });
    }

    var loadingBarWidth = 20;
    var MAX_LOADING_WIDTH = 95;

    $('.loading-bar').addClass('loading');
    $('.loading-bar__progress').css('width', loadingBarWidth + '%');
    clearInterval(loadingTimer);
    loadingTimer = setInterval(function () {
      loadingBarWidth += 3;
      if (loadingBarWidth > MAX_LOADING_WIDTH) {
        loadingBarWidth = MAX_LOADING_WIDTH;
      }
      $('.loading-bar__progress').css('width', loadingBarWidth + '%');
    }, 500);
  }, false);

  window.addEventListener('pjax:complete', function () {
    clearInterval(loadingTimer);
    $('.loading-bar__progress').css('width', '100%');
    $('.loading-bar').removeClass('loading');
    setTimeout(function () {
      $('.loading-bar__progress').css('width', '0');
    }, 400);
    $('link[rel=prefetch], script[data-pjax-rm]').each(function () {
      $(this).remove();
    });
    $('script[data-pjax], #pjax-reload script').each(function () {
      $(this).parent().append($(this).remove());
    });

    if (Stun.utils.pjaxReloadBoot) {
      Stun.utils.pjaxReloadBoot();
    }
    if (Stun.utils.pjaxReloadScroll) {
      Stun.utils.pjaxReloadScroll();
    }
    if (Stun.utils.pjaxReloadSidebar) {
      Stun.utils.pjaxReloadSidebar();
    }
    if (false) {
      if (Stun.utils.pjaxReloadHeader) {
        Stun.utils.pjaxReloadHeader();
      }
      if (Stun.utils.pjaxReloadScrollIcon) {
        Stun.utils.pjaxReloadScrollIcon();
      }
      if (Stun.utils.pjaxReloadLocalSearch) {
        Stun.utils.pjaxReloadLocalSearch();
      }
    }
  }, false);
}, false);</script><div id="pjax-reload"></div><script src="/js/utils.js?v=2.0.1"></script><script src="/js/stun-boot.js?v=2.0.1"></script><script src="/js/scroll.js?v=2.0.1"></script><script src="/js/header.js?v=2.0.1"></script><script src="/js/sidebar.js?v=2.0.1"></script></body></html>